﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;

namespace Cinser.DBUtility.DAL
{

    public class DBHelperAccess
    {
        public OleDbConnection conn = null;
        private OleDbCommand cmd = null;
        private OleDbDataAdapter adapter = null;
        private bool isTrans = false;
        private OleDbTransaction trans = null;
        public string DbPath = "";
        string pwd = string.Empty;
        public DBHelperAccess(string dbPath)
        {
            this.DbPath = dbPath;
        }
        public DBHelperAccess(string dbPath,string pwd)
        {
            this.DbPath = dbPath;
            this.pwd = pwd;
        }
        /**
         * 获取数据库连接对象
         * */
        public void getConn()
        {
            String connStr = "";
            if (System.IO.File.Exists(DbPath))
            {
                if (string.IsNullOrEmpty(pwd))
                    connStr = @" Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + DbPath;
                else
                    connStr = @" Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + DbPath + ";Jet OLEDB:Database Password=" + pwd + ";";

            }
            else
                connStr = DbPath;
            conn = new OleDbConnection(connStr);
        }
        /**
         * 打开数据库连接
         * */
        public void openConn()
        {
            getConn();
            if ((conn != null) && (conn.State == System.Data.ConnectionState.Closed))
            {
                conn.Open();
            }
        }
        /**
         *  关闭数据库连接
         * */
        public void closeConn()
        {
            if ((conn != null) && (conn.State != System.Data.ConnectionState.Closed) && !isTrans)
            {
                conn.Close();
            }
        }
        /**
         * 开始事务
         * */
        public void beginTrans()
        {
            openConn();
            isTrans = true;
            trans = conn.BeginTransaction();
        }
        /**
         * 提交事务
         * */
        public void commitTrans()
        {
            trans.Commit();
            isTrans = false;
            closeConn();
        }
        /**
         * 回滚事务
         * */
        public void rollbackTrans()
        {
            trans.Rollback();
            isTrans = false;
            closeConn();
        }
        /**
         * 执行DML语句
         * */
        public void execSql(string sql)
        {
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }

            cmd.CommandText = sql;
            cmd.ExecuteNonQuery();
            closeConn();
        }
        /// <summary>
        /// 执行多条Sql 语句
        /// </summary>
        /// <param name="sqls"></param>
        public void execSqls(string[] sqls)
        {
            openConn();
            cmd = conn.CreateCommand();
            for (int i = 0; i < sqls.Length; i++)
            {
                cmd.CommandText = sqls[i];
                cmd.ExecuteNonQuery();
            }
            closeConn();
        }
        public void execSqls(string[] sqls, List<List<OleDbParameter>> parasList)
        {
            openConn();
            for (int i = 0; i < sqls.Length; i++)
            {
                cmd = conn.CreateCommand();
                cmd.CommandText = sqls[i];
                for (int j = 0; j < parasList[i].Count; j++)
                {
                    cmd.Parameters.Add(parasList[i][j]);
                    // cmd.Parameters.Add(parasList[i][j].ParameterName, parasList[i][j].Value);
                }
                //string str=  cmd.CommandText.ToString();
                cmd.ExecuteNonQuery();
            }
            closeConn();
        }
        /**
         * 执行DML语句（可带参数）
         * */
        public int execSql(string sql, List<OleDbParameter> para)
        {
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }

            cmd.CommandText = sql;
            for (int i = 0; i < para.Count; i++)
            {
                cmd.Parameters.Add(para[i]);
            }
            int result = cmd.ExecuteNonQuery();
            closeConn();
            return result;
        }
        public object execScalar(string sql, List<OleDbParameter> para)
        {
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }

            cmd.CommandText = sql;
            for (int i = 0; i < para.Count; i++)
            {
                cmd.Parameters.Add(para[i]);
            }
            object result = cmd.ExecuteScalar();
            closeConn();
            return result;
        }
        /**
         * 执行DML语句，并返回单独一列的值
         * */
        public object execScalar(string sql)
        {
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
            }
            cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            object returnval = cmd.ExecuteScalar();
            closeConn();
            return returnval;
        }
        /**
         * 执行DML语句，并返回影响行数
         * */
        public int execSqlREF(string sql)
        {
            int J;
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }

            cmd.CommandText = sql;
            J = cmd.ExecuteNonQuery();
            closeConn();
            return J;
        }
        /**
         * 执行DML语句（可带参数）并返回影响行数
         * */
        public int execSqlREF(string sql, OleDbCommand[] para)
        {
            int J = 0;
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }
            cmd.CommandText = sql;
            if (para != null)
            {
                for (int i = 0; i < para.Length; i++)
                {
                    cmd.Parameters.Add(para[i]);
                }
            }
            J = cmd.ExecuteNonQuery();
            closeConn();
            return J;
        }
        /**
         * 通过SQL语句返回结果集
         * */
        public System.Data.DataSet getDataSet(string sql)
        {
            DataSet dataSet = new DataSet();
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
                cmd = conn.CreateCommand();
            }
            cmd.CommandText = sql;
            adapter = new OleDbDataAdapter(cmd);
            adapter.Fill(dataSet);
            closeConn();
            return dataSet;
        }
        public System.Data.DataTable getColumnsType(string tablename)
        {
            openConn();
            DataTable dt = conn.GetSchema("columns", new string[] { null, null, tablename });
            dt.DefaultView.Sort = "ordinal_position";

            //DataView dv = new DataView(dt);
            //dv.Sort = "ordinal_position";

            //DataTable dtnew = dv.ToTable();

            //dt.DefaultView.ToTable();

            conn.Close();

            return dt.DefaultView.ToTable();
        }
        /**
        * 通过SQL语句返回结果集
        * */
        public System.Data.DataSet getDataSet(string sql, OleDbParameter[] para)
        {
            DataSet dataSet = new DataSet();
            try
            {
                if (isTrans)
                {
                    cmd = conn.CreateCommand();
                    cmd.Transaction = trans;
                }
                else
                {
                    openConn();
                }
                cmd = getSqlCmd(sql, para);
                adapter = new OleDbDataAdapter(cmd);
                adapter.Fill(dataSet);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return dataSet;
        }
        /**
         * 将参数传入Command对象后返回该Command对象
         **/
        private OleDbCommand getSqlCmd(string sql, OleDbParameter[] para)
        {
            OleDbCommand command = conn.CreateCommand();
            command.CommandText = sql;
            for (int i = 0; i < para.Length; i++)
            {
                command.Parameters.Add(para[i]);
            }
            return command;
        }


        public DataTable GetSchemaTable(string tableName)
        {
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
            }

            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, tableName, null });
            //cmd = conn.CreateCommand();
            //cmd.CommandText = sql;
            //object returnval = cmd.ExecuteScalar();
            closeConn();
            return dt;



            //将查询到的数据按照字段在表中的顺序排序
            //dt.DefaultView.Sort = "ordinal_position asc";
            //DataView v_dvColumn = v_dt.DefaultView;


        }

        /// <summary>
        /// 取所有表名
        /// </summary>
        /// <returns></returns>
        public DataTable GetSchema(string tbName)
        {
            if (isTrans)
            {
                cmd = conn.CreateCommand();
                cmd.Transaction = trans;
            }
            else
            {
                openConn();
            }
            DataTable dt = conn.GetSchema(tbName);
            closeConn();
            return dt;
        }
    }
}
